package br.com.fiap.trabalho.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;

import java.util.List;

import br.com.fiap.trabalho.entity.Actor;
import br.com.fiap.trabalho.entity.Movie;

public class JDBCActorDAO implements ActorDAO {

	private Connection connection;
	private PreparedStatement preparedStatement;

	private boolean openConnection() throws Exception {
		try {
			Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
			String url = "jdbc:derby:simpleDB;create=true";
			connection = DriverManager.getConnection(url,"TrabalhoPersistenciaJava", "TrabalhoPersistenciaJava");
			return true;
		} catch (Exception ex) {
			throw ex;
		}
	}

	private void closeConnection() {
		try {
			connection.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	// Tempo: 20 minutos.
	public Actor createActor(Actor actor) {
		try {
			openConnection();
			preparedStatement = connection.prepareStatement("INSERT INTO ACTOR (FULLNAME, BIRTHDATE) VALUES (?,?)", 
					Statement.RETURN_GENERATED_KEYS);
			preparedStatement.setString(1, actor.getFullName());
			preparedStatement.setDate(2, new java.sql.Date(actor.getBirthDate().getTime()));
			preparedStatement.executeUpdate();
			
			ResultSet rs = preparedStatement.getGeneratedKeys();
			if(rs.next()) 
			{
				actor.setId( rs.getInt(1));
			}
			
		} catch (Exception e) {
			e.printStackTrace();
			try {
				connection.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
		} finally {
			try {
				preparedStatement.close();
				closeConnection();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return actor;
	}

	// Tempo: 25 minutos.
	public boolean deleteActor(Actor actor) {
		boolean return1 = true;
		try {
			openConnection();
			connection.setAutoCommit(false);
			preparedStatement = connection.prepareStatement("DELETE FROM MOVIE_ACTOR WHERE ID_ACTOR = ?");
			preparedStatement.setInt(1, actor.getId());
			preparedStatement.executeUpdate();
			preparedStatement = connection.prepareStatement("DELETE FROM ACTOR WHERE ID_ACTOR = ?");
			preparedStatement.setInt(1, actor.getId());
			preparedStatement.executeUpdate();
			connection.commit();
		} catch (Exception e) {
			e.printStackTrace();
			try {
				connection.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			return1 = false;
		} finally {
			try {
				connection.setAutoCommit(true);
				preparedStatement.close();
				closeConnection();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return return1;
	}

	// Tempo: 20 minutos.
	public List<Actor> selectActorByName(String name) {
		try {
			List<Actor> listActor = new ArrayList<Actor>();
			openConnection();
			preparedStatement = connection.prepareStatement("SELECT ID_ACTOR AS ID, FULLNAME, BIRTHDATE FROM ACTOR WHERE FULLNAME = ?");
			preparedStatement.setString(1, name);
			ResultSet resultSet = preparedStatement.executeQuery();
			Actor actor;
			while (resultSet.next()) {
				actor = new Actor();
				actor.setId(resultSet.getInt(1));
				actor.setFullName(resultSet.getString(2));
				actor.setBirthDate(resultSet.getDate(3));
				listActor.add(actor);
			}
			return listActor;
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		} finally {
			try {
				preparedStatement.close();
				closeConnection();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	// Tempo: 60 minutos.
	public List<Actor> selectActorByAge(int age) {
		SimpleDateFormat formatador = new SimpleDateFormat("yyyy-MM-dd");
		Calendar today = Calendar.getInstance();
		String yearOfBirth = Integer.toString((today.get(Calendar.YEAR)-age));
		java.util.Date startDate = new java.util.Date();
		java.util.Date finishDate = new java.util.Date();
		try {
			startDate = formatador.parse(yearOfBirth+"-01-01");
			finishDate = formatador.parse(yearOfBirth+"-12-31");
		} catch (ParseException e1) {
			e1.printStackTrace();
		}
		try {
			List<Actor> listActor = new ArrayList<Actor>();
			openConnection();
			preparedStatement = connection.prepareStatement("SELECT ID_ACTOR, FULLNAME, BIRTHDATE FROM ACTOR WHERE BIRTHDATE BETWEEN ? AND ?");
			preparedStatement.setTimestamp(1, new java.sql.Timestamp((startDate).getTime()));
			preparedStatement.setTimestamp(2, new java.sql.Timestamp((finishDate.getTime())));
			ResultSet resultSet = preparedStatement.executeQuery();
			Actor actor;
			while (resultSet.next()) {
				actor = new Actor();
				actor.setId(resultSet.getInt(1));
				actor.setFullName(resultSet.getString(2));
				actor.setBirthDate(resultSet.getDate(3));
				listActor.add(actor);
			}
			return listActor;
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		} finally {
			try {
				preparedStatement.close();
				closeConnection();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
	// Tempo: 35 minutos.
	public List<Actor> selectActorByMovie(Movie movie) {	
		try {
			List<Actor> listActorByMovie = new ArrayList<Actor>();
			openConnection();
			preparedStatement = connection.prepareStatement("SELECT A.ID_ACTOR, A.FULLNAME  FROM ACTOR AS A INNER JOIN MOVIE_ACTOR AS MA ON (A.ID_ACTOR = MA.ID_ACTOR) INNER JOIN MOVIE AS M ON (MA.ID_MOVIE = M.ID_MOVIE) WHERE M.ID_MOVIE = ?");
			preparedStatement.setInt(1, movie.getId());
			ResultSet resultSet = preparedStatement.executeQuery();
			Actor actor;
			while (resultSet.next()) {
				actor = new Actor();
				actor.setId(resultSet.getInt(1));
				actor.setFullName(resultSet.getString(2));
				listActorByMovie.add(actor);
			}
			return listActorByMovie;
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		} finally {
			try {
				preparedStatement.close();
				closeConnection();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
}
